package Database
{
	import Database.Keys.*;
	
	import flash.data.SQLConnection;
	import flash.data.SQLResult;
	import flash.data.SQLStatement;
	import flash.errors.SQLError;
	import flash.events.SQLEvent;
	import flash.filesystem.File;
	
	internal class DatabaseUtility
	{
		private const DB_LOCATION:File = File.applicationStorageDirectory.resolvePath("storage.db");
		private static var dbUtility:DatabaseUtility;
		
		public function DatabaseUtility(accessKey:int)
		{
			if(accessKey!=1989623)
				throw new Error("DatabaseUtility(): Access Denied: use init() instead");
		}
		
		static public function init():DatabaseUtility
		{
			if(dbUtility==null)
				dbUtility = new DatabaseUtility(1989623);
			return dbUtility;
		}
		
		private function openConnection():SQLConnection
		{
			var conn:SQLConnection = new SQLConnection();
			conn.open(DB_LOCATION);
			return conn;
		}
		
		public function removeColumns(tableName:String,key:DatabaseKey):void
		{
			executeNonQuery("DELETE from "+tableName+" where "+key.name+" = "+key.value);
		}
		
		private function createTableIfNotExists(tableName:String,params:Vector.<DatabaseKey>):void
		{
			var attributes:String;
			if(params.length>0)
				attributes = params[0].queryString;
			for(var i:int=1;i<params.length; i++)
			{
				attributes += ","+params[i].queryString;
			}
			executeNonQuery("CREATE TABLE if not exists "+tableName+"("+attributes+")");
		}
		
		public function insertIntoTable(tableName:String,params:Vector.<DatabaseKey>):void
		{
			createTableIfNotExists(tableName,params);
			
			var values:String;
			var columns:String;
			if(params.length>0)
			{
				values= params[0].value;
				columns = params[0].name;
			}
			for(var i:int=1;i<params.length;i++)
			{
				values += ","+params[i].value;
				columns += ","+params[i].name;
			}
			executeNonQuery("INSERT INTO "+ tableName +" ("+columns+") VALUES ("+values+")");
		}
		
		public function leftOuterJoinTwoTables(tableOneName:String,tableTwoName:String, joinParamOne:String, joinParamTwo:String, callback:Function):void
		{
			executeQuery("SELECT * FROM "+tableOneName+" LEFT OUTER JOIN "+tableTwoName+" ON "+joinParamOne+"="+joinParamTwo+" ORDER BY "+joinParamOne,callback);
		}
		
		private function executeNonQuery(query:String):void
		{
			var conn:SQLConnection = openConnection();
			var statement:SQLStatement = new SQLStatement();
			statement.sqlConnection = conn;
			statement.text = query;
			statement.execute();
			conn.close();
		}
		
		private static var readerCallback:Function=null;
		private function executeQuery(query:String,callback:Function):void
		{
			var conn:SQLConnection = openConnection();
			readerCallback = callback;
			var statement:SQLStatement = new SQLStatement();
			statement.addEventListener(SQLEvent.RESULT, triggerReaderCallback);
			statement.sqlConnection = conn;
			statement.text = query;
			statement.execute();
			conn.close();
		}
		
		private function triggerReaderCallback(e:SQLEvent):void
		{
			e.target.removeEventListener(SQLEvent.RESULT, triggerReaderCallback);
			var result:SQLResult = e.target.getResult();
			readerCallback(result.data);
			readerCallback=null;
		}
	}
}